package com.yunquan.ohana.db

import android.annotation.SuppressLint
import android.content.ContentValues
import android.database.Cursor
import com.yunquan.ohana.entity.FileGroupEntity
import com.yunquan.ohana.entity.LocalFileEntity
import com.yunquan.ohana.entity.LocalFrameEntity

class DBPhoto {
    companion object {
        val instance by lazy(LazyThreadSafetyMode.SYNCHRONIZED) { DBPhoto() }
    }

    private val readDB = DBManager.instance.readableDatabase
    private val writeDB = DBManager.instance.writableDatabase

    fun saveOrUpdate(data: LocalFileEntity): Boolean {
        return if (data.id != null) {
            update(data)
        } else {
            insert(data)
        }
    }

    fun insert(data: LocalFileEntity): Boolean {
        val result = writeDB.insert(DBManager.TABLE_PHOTO, null, toContentValues(data))
        return result != -1L
    }

    fun update(data: LocalFileEntity): Boolean {
        if (data.id == null) return false
        val result = writeDB.update(DBManager.TABLE_PHOTO, toContentValues(data), "id = ?", arrayOf(data.id.toString()))
        return result != 0
    }

    fun updateStatus(groupId: String, status: Int): Boolean {
        val values = ContentValues()
        values.put("status", status)
        val result = writeDB.update(DBManager.TABLE_PHOTO, values, "group_id = ?", arrayOf(groupId))
        return result != 0
    }

    fun updateSendingToFailed(): Boolean {
        val values = ContentValues()
        values.put("status", 2)
        val result = writeDB.update(DBManager.TABLE_PHOTO, values, "status = ?", arrayOf("0"))
        return result != 0
    }

    fun delete(id: Int): Boolean {
        val result = writeDB.delete(DBManager.TABLE_PHOTO, "id = ?", arrayOf(id.toString()))
        return result != 0
    }

    fun delete(groupId: String): Boolean {
        val result = writeDB.delete(DBManager.TABLE_PHOTO, "group_id = ?", arrayOf(groupId))
        return result != 0
    }

    fun selectOne(id: Int): LocalFileEntity? {
        val cursor = readDB.query(DBManager.TABLE_PHOTO, null, "id = ?", arrayOf(id.toString()), null, null, null)
        var data: LocalFileEntity? = null
        if (cursor.moveToFirst()) {
            data = toEntity(cursor)
        }
        cursor.close()
        return data
    }

    fun selectByGroupId(groupId: String): List<LocalFileEntity> {
        val cursor = readDB.query(DBManager.TABLE_PHOTO, null, "group_id = ?", arrayOf(groupId), null, null, "create_time desc")
        val data = mutableListOf<LocalFileEntity>()
        while (cursor.moveToNext()) {
            data.add(toEntity(cursor))
        }
        cursor.close()
        return data
    }

    fun selectAll(): List<LocalFileEntity> {
        val cursor = readDB.query(DBManager.TABLE_PHOTO, null, null, null, null, null, null)
        val data = mutableListOf<LocalFileEntity>()
        while (cursor.moveToNext()) {
            data.add(toEntity(cursor))
        }
        cursor.close()
        return data
    }

    @SuppressLint("Range")
    fun selectGroupPage(status: Int, page: Int, size: Int): List<FileGroupEntity> {
        val offset = (page - 1) * size
        val sql = "SELECT group_id FROM ${DBManager.TABLE_PHOTO} WHERE status = ? GROUP BY group_id ORDER BY create_time desc LIMIT ? OFFSET ?"
        val cGroup = readDB.rawQuery(sql, arrayOf(status.toString(), size.toString(), offset.toString()))
        val data = mutableListOf<FileGroupEntity>()
        while (cGroup.moveToNext()) {
            val groupId = cGroup.getString(cGroup.getColumnIndex("group_id"))
            val cPhoto = readDB.query(
                DBManager.TABLE_PHOTO,
                null,
                "group_id = ?",
                arrayOf(groupId),
                null,
                null,
                "create_time desc"
            )
            val photos = mutableListOf<LocalFileEntity>()
            while (cPhoto.moveToNext()) {
                photos.add(toEntity(cPhoto))
            }
            cPhoto.close()
            val cFrames = readDB.query(
                DBManager.TABLE_FRAMES,
                null,
                "device_id IN (SELECT frames_id FROM ${DBManager.TABLE_PHOTO_FRAMES} WHERE group_id = ?)",
                arrayOf(groupId),
                null,
                null,
                null
            )
            val frames = mutableListOf<LocalFrameEntity>()
            while (cFrames.moveToNext()) {
                frames.add(DBFrames.instance.toEntity(cFrames))
            }
            cFrames.close()
            data.add(
                FileGroupEntity(
                    groupId = groupId,
                    serviceGroupId = if (photos.isNotEmpty()) photos.first().serviceGroupId else null,
                    photos = photos,
                    frames = frames
                )
            )
        }
        cGroup.close()
        return data
    }

    @SuppressLint("Range")
    fun selectGroupAll(status: Int): List<FileGroupEntity> {
        val cGroup = readDB.query(
            DBManager.TABLE_PHOTO,
            arrayOf("group_id"),
            "status = ?",
            arrayOf(status.toString()),
            "group_id",
            null,
            "create_time desc"
        )
        val data = mutableListOf<FileGroupEntity>()
        while (cGroup.moveToNext()) {
            val groupId = cGroup.getString(cGroup.getColumnIndex("group_id"))
            val cPhoto = readDB.query(
                DBManager.TABLE_PHOTO,
                null,
                "group_id = ?",
                arrayOf(groupId),
                null,
                null,
                "create_time desc"
            )
            val photos = mutableListOf<LocalFileEntity>()
            while (cPhoto.moveToNext()) {
                photos.add(toEntity(cPhoto))
            }
            cPhoto.close()
            val cFrames = readDB.query(
                DBManager.TABLE_FRAMES,
                null,
                "device_id IN (SELECT frames_id FROM ${DBManager.TABLE_PHOTO_FRAMES} WHERE group_id = ?)",
                arrayOf(groupId),
                null,
                null,
                null
            )
            val frames = mutableListOf<LocalFrameEntity>()
            while (cFrames.moveToNext()) {
                frames.add(DBFrames.instance.toEntity(cFrames))
            }
            cFrames.close()
            data.add(
                FileGroupEntity(
                    groupId = groupId,
                    serviceGroupId = if (photos.isNotEmpty()) photos.first().serviceGroupId else null,
                    photos = photos,
                    frames = frames
                )
            )
        }
        cGroup.close()
        return data
    }

    @SuppressLint("Range")
    private fun toEntity(cursor: Cursor): LocalFileEntity {
        val id = cursor.getInt(cursor.getColumnIndex("id"))
        val serviceId = cursor.getString(cursor.getColumnIndex("service_id"))
        val groupId = cursor.getString(cursor.getColumnIndex("group_id"))
        val serviceGroupId = cursor.getString(cursor.getColumnIndex("service_group_id"))
        val content = cursor.getString(cursor.getColumnIndex("content"))
        val fileName = cursor.getString(cursor.getColumnIndex("file_name"))
        val fileUrl = cursor.getString(cursor.getColumnIndex("file_url"))
        val localFileId = cursor.getString(cursor.getColumnIndex("local_file_id"))
        val localFilePath = cursor.getString(cursor.getColumnIndex("local_file_path"))
        val localCropId = cursor.getString(cursor.getColumnIndex("local_crop_id"))
        val localCropPath = cursor.getString(cursor.getColumnIndex("local_crop_path"))
        val videoImgUrl = cursor.getString(cursor.getColumnIndex("video_img_url"))
        val thumbnail = cursor.getString(cursor.getColumnIndex("thumbnail"))
        val duration = cursor.getInt(cursor.getColumnIndex("duration"))
        val height = cursor.getInt(cursor.getColumnIndex("height"))
        val width = cursor.getInt(cursor.getColumnIndex("width"))
        val status = cursor.getInt(cursor.getColumnIndex("status"))
        val type = cursor.getInt(cursor.getColumnIndex("type"))
        val createTime = cursor.getString(cursor.getColumnIndex("create_time"))
        return LocalFileEntity(
            id = id,
            serviceId = serviceId,
            groupId = groupId,
            serviceGroupId = serviceGroupId,
            content = content,
            fileName = fileName,
            fileUrl = fileUrl,
            localFileId = localFileId,
            localFilePath = localFilePath,
            localCropId = localCropId,
            localCropPath = localCropPath,
            videoImgUrl = videoImgUrl,
            thumbnail = thumbnail,
            duration = duration,
            height = height,
            width = width,
            status = status,
            type = type,
            createTime = createTime.toLong(),
        )
    }

    private fun toContentValues(data: LocalFileEntity): ContentValues {
        val values = ContentValues()
        if (data.id != null) {
            values.put("id", data.id)
        }
        if (data.serviceId != null) {
            values.put("service_id", data.serviceId)
        }
        if (data.groupId != null) {
            values.put("group_id", data.groupId)
        }
        if (data.serviceGroupId != null) {
            values.put("service_group_id", data.serviceGroupId)
        }
        if (data.content != null) {
            values.put("content", data.content)
        }
        if (data.fileName != null) {
            values.put("file_name", data.fileName)
        }
        if (data.fileUrl != null) {
            values.put("file_url", data.fileUrl)
        }
        if (data.localFileId != null) {
            values.put("local_file_id", data.localFileId)
        }
        if (data.localFilePath != null) {
            values.put("local_file_path", data.localFilePath)
        }
        if (data.localCropId != null) {
            values.put("local_crop_id", data.localCropId)
        }
        if (data.localCropPath != null) {
            values.put("local_crop_path", data.localCropPath)
        }
        if (data.videoImgUrl != null) {
            values.put("video_img_url", data.videoImgUrl)
        }
        if (data.thumbnail != null) {
            values.put("thumbnail", data.thumbnail)
        }
        if (data.duration != null) {
            values.put("duration", data.duration)
        }
        if (data.height != null) {
            values.put("height", data.height)
        }
        if (data.width != null) {
            values.put("width", data.width)
        }
        if (data.status != null) {
            values.put("status", data.status)
        }
        if (data.type != null) {
            values.put("type", data.type)
        }
        if (data.createTime != null) {
            values.put("create_time", data.createTime.toString())
        }
        return values
    }
}